package cpr.command.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import cpr.DefaultCommandHandler;
import cpr.Printer;

public class CmdDbCopyTable extends DefaultCommandHandler {
	public CmdDbCopyTable() {
		super("db-copy-table");
	}

	public void execute(String[] params) {
		// db-copy-table jdbc:odbc:getit jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass PCUSTOMER_2007_07_05
		// db-copy-table jdbc:odbc:getit jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass PMATERIAL_2007_07_05
		// db-copy-table jdbc:odbc:getit jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass ZDU_CPRN_RULES_2007_07_05
		// db-copy-table jdbc:odbc:getit jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass ZDU_CPRN_DUML4_2007_07_05
		// db-copy-table jdbc:odbc:DUSVLTCH jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass [Sheet1$]
		// db-copy-table jdbc:odbc:DUSVLTCH jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass [DUSVLTCH$] DUSVLTCH
		// db-copy-table jdbc:odbc:PSALESORG jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass [PSALESORG$] PSALESORG
		// db-copy-table jdbc:odbc:TDUSVLTCH jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass [TDUSVLTCH$] TDUSVLTCH
		

		
		
		String jdbcSourceString = getParameter(params, 1); //"jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass"
		String jdbcTargetString = getParameter(params, 2); //"jdbc:mysql://localhost/cpr01?user=cpr01user&password=cprpass"
		String sourceTableName = getParameter(params, 3);
		String targetTableName = getParameter(params, 4, sourceTableName);

		Statement srcStmt = null;
		ResultSet srcRs = null;
		Connection srcConn = null;
		Statement trgStmt = null;
		Connection trgConn = null;
		String sql = null;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Class.forName("com.mysql.jdbc.Driver");

			srcConn = DriverManager.getConnection(jdbcSourceString);
			srcStmt = srcConn.createStatement();
			srcRs = srcStmt.executeQuery("SELECT * FROM " + sourceTableName);

			trgConn = DriverManager.getConnection(jdbcTargetString);
			trgStmt = trgConn.createStatement();

			
			ResultSetMetaData md = srcRs.getMetaData();
			StringBuffer sb = new StringBuffer();
			StringBuffer insCols = new StringBuffer();
			StringBuffer createCols = new StringBuffer();
			for (int i = 1; i <= md.getColumnCount(); i++) {
				String name = md.getColumnName(i);
				sb.append(name + "\t");
				if (insCols.length()>0) {
					insCols.append(" , ");
				}
				insCols.append("`"+name+"`");

				if (createCols.length()>0) {
					createCols.append(" , ");
				}
				createCols.append("`"+name+"` VARCHAR( 255 )");
			}
			
			sql = "DROP TABLE `"+targetTableName+"`";
			
			try {
				trgStmt.execute(sql);
			} catch (Exception ex) {
				
			}
			
			sql = "CREATE TABLE `"+targetTableName+"`( "
					+ createCols.toString()
					+ " ) ";
			trgStmt.execute(sql);

			
			
			int count = 0;
			while (srcRs.next()) {
				count++;
				StringBuffer valuesSb = new StringBuffer();
				for (int i = 1; i <= md.getColumnCount(); i++) {
					String name = md.getColumnName(i);
					String value = srcRs.getString(name);
					if (valuesSb.length()>0) {
						valuesSb.append(" , ");
					}
					if (value!=null) {
						valuesSb.append("'"+value +"'");
					} else {
						valuesSb.append("NULL");
					}
				}
				
				sql = "INSERT INTO `"+targetTableName+"` ( " + insCols.toString()+" )\n"
				+ "VALUES ( "
				+ valuesSb.toString()
				+" )";
				trgStmt.execute(sql);
				if ((count & ((1<<7)-1)) ==((1<<7)-1) ) {
					Printer.print(".");
				}
				if ((count & ((1<<13)-1)) ==((1<<13)-1) ) {
					Printer.println("");
				}
				
			}
			Printer.println("");
			Printer.println("" + count + " records copied.");

		} catch (ClassNotFoundException ex) {
			System.out.println("ClassNotFoundException: " + ex.getMessage());

		} catch (SQLException ex) {
			// handle any errors
			Printer.println(sql);
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		} finally {
			// it is a good idea to release
			// resources in a finally{} block
			// in reverse-order of their creation
			// if they are no-longer needed

			if (srcRs != null) {
				try {
					srcRs.close();
				} catch (SQLException sqlEx) { // ignore }
					srcRs = null;
				}

				if (srcStmt != null) {
					try {
						srcStmt.close();
					} catch (SQLException sqlEx) { // ignore }
						srcStmt = null;
					}
				}
				
				if (trgStmt != null) {
					try {
						trgStmt.close();
					} catch (SQLException sqlEx) { // ignore }
						trgStmt = null;
					}
				}
			}
		}

	}

}
